#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import psycopg2
import json
import random
import os
from dotenv import load_dotenv, find_dotenv

# 在模块加载时初始化 .env（不覆盖已有系统环境变量）
dotenv_path = find_dotenv()
if dotenv_path:
    load_dotenv(dotenv_path=dotenv_path, override=False)

def get_db_config():
    """读取数据库连接配置，优先使用环境变量并支持 .env

    返回值为 psycopg2 兼容的配置字典，其中 `port` 为整数。
    - 环境变量键：`DB_HOST`, `DB_NAME`, `DB_USER`, `DB_PASSWORD`, `DB_PORT`
    - 默认值与原逻辑保持一致，确保可直接运行
    """
    return {
        'host': os.getenv('DB_HOST', 'localhost'),
        'database': os.getenv('DB_NAME', 'postgres'),
        'user': os.getenv('DB_USER', 'lijun'),
        'password': os.getenv('DB_PASSWORD', 'kuaile9hao'),
        'port': int(os.getenv('DB_PORT', 5432))
    }

def get_random_songs(limit=50):
    """从数据库中随机获取指定数量的歌曲"""
    db_config = get_db_config()
    
    try:
        conn = psycopg2.connect(**db_config)
        cursor = conn.cursor()
        
        # 随机获取歌曲数据
        query = """
        SELECT m.id, m.rid, m.name, m.artist, m.pic, m.src,
               ARRAY_AGG(md.formatted_url) as download_urls
        FROM music m
        LEFT JOIN music_download_url md ON m.id = md.music_id
        WHERE m.src IS NOT NULL AND m.src != ''
        GROUP BY m.id, m.rid, m.name, m.artist, m.pic, m.src
        ORDER BY RANDOM()
        LIMIT %s
        """
        
        cursor.execute(query, (limit,))
        songs = cursor.fetchall()
        
        # 格式化数据
        result = []
        for song in songs:
            result.append({
                'id': song[0],
                'rid': song[1],
                'name': song[2],
                'artist': song[3],
                'pic': song[4],
                'src': song[5],
                'download_urls': [url for url in song[6] if url] if song[6] else []
            })
        
        cursor.close()
        conn.close()
        
        return result
    except Exception as e:
        print(f"数据库查询出错: {e}")
        if 'conn' in locals():
            conn.close()
        return []

def get_song_download_urls(song_id):
    """获取指定歌曲的下载链接"""
    db_config = get_db_config()
    
    try:
        conn = psycopg2.connect(**db_config)
        cursor = conn.cursor()
        
        query = """
        SELECT url_type, url_value, formatted_url
        FROM music_download_url
        WHERE music_id = %s
        ORDER BY id
        """
        
        cursor.execute(query, (song_id,))
        download_urls = cursor.fetchall()
        
        result = []
        for url_type, url_value, formatted_url in download_urls:
            result.append({
                'type': url_type,
                'value': url_value,
                'formatted': formatted_url
            })
        
        cursor.close()
        conn.close()
        
        return result
    except Exception as e:
        print(f"获取下载链接出错: {e}")
        if 'conn' in locals():
            conn.close()
        return []

def search_songs(keyword, limit=50):
    """根据关键字搜索歌曲"""
    db_config = get_db_config()
    
    try:
        conn = psycopg2.connect(**db_config)
        cursor = conn.cursor()
        
        # 搜索歌曲数据
        query = """
        SELECT m.id, m.rid, m.name, m.artist, m.pic, m.src,
               ARRAY_AGG(md.formatted_url) as download_urls
        FROM music m
        LEFT JOIN music_download_url md ON m.id = md.music_id
        WHERE (m.name ILIKE %s OR m.artist ILIKE %s)
              AND m.src IS NOT NULL AND m.src != ''
        GROUP BY m.id, m.rid, m.name, m.artist, m.pic, m.src
        ORDER BY 
            CASE 
                WHEN m.name ILIKE %s THEN 1
                WHEN m.artist ILIKE %s THEN 2
                ELSE 3
            END,
            m.name
        LIMIT %s
        """
        
        search_pattern = f'%{keyword}%'
        cursor.execute(query, (search_pattern, search_pattern, search_pattern, search_pattern, limit))
        songs = cursor.fetchall()
        
        # 格式化数据
        result = []
        for song in songs:
            result.append({
                'id': song[0],
                'rid': song[1],
                'name': song[2],
                'artist': song[3],
                'pic': song[4],
                'src': song[5],
                'download_urls': [url for url in song[6] if url] if song[6] else []
            })
        
        cursor.close()
        conn.close()
        
        return result
    except Exception as e:
        print(f"搜索歌曲出错: {e}")
        if 'conn' in locals():
            conn.close()
        return []

if __name__ == "__main__":
    # 测试获取随机歌曲
    songs = get_random_songs(50)
    print(json.dumps(songs, ensure_ascii=False, indent=2))